﻿
CREATE PROCEDURE [dbo].[exbug_ListaCheltuieliTotale]
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@IDJurnal int
AS
BEGIN
	SET NOCOUNT ON;

	SELECT a.ArtAl,a.Denumire,sum(CASE WHEN d.IDTipDepartament IN(16,18,22) THEN e.Valoare else 0 END) Chelt_directe 
	,sum(CASE WHEN d.IDTipDepartament not IN(16,18,22) THEN e.Valoare else 0 END) Chelt_indirecte 
	,sum(e.Valoare) Chelt_totale
	FROM Articolealiniate a LEFT OUTER JOIN ExecutieCheltuieli e ON dbo.IsContInclus(a.ArtAl,e.ArtAl,a.Terminal)=1 AND a.CodUnitate = e.CodUnitate
	JOIN Departamente d ON e.IDDepartament = d.IDDepartament AND d.Activ=1
	WHERE a.CodUnitate = @CodUnitate AND Data>=@DeLaData and Data <=@PanaLaData
	--AND IDJurnal=@IDJurnal
	GROUP BY a.ArtAl,a.Denumire
	having SUM(Valoare)<>0 
	
	UNION	
	SELECT '1' ArtAl,'CHELTUIELI TOTALE' denumire,sum(CASE WHEN d.IDTipDepartament IN(16,18,22) THEN e.Valoare else 0 END) Chelt_directe 
	,sum(CASE WHEN d.IDTipDepartament not IN(16,18,22) THEN e.Valoare else 0 END) Chelt_indirecte 
	,sum(e.Valoare) Chelt_totale
	FROM Articolealiniate a LEFT OUTER JOIN ExecutieCheltuieli e ON e.ArtAl=a.ArtAl AND a.CodUnitate = e.CodUnitate
	JOIN Departamente d ON e.IDDepartament = d.IDDepartament AND d.Activ=1
	WHERE a.CodUnitate = @CodUnitate AND Data>=@DeLaData and Data <=@PanaLaData 
	--AND IDJurnal=@IDJurnal	
	Order by a.ArtAl
END